﻿using Tiger.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Tiger.Common
{
    public class DbUtil
    {
        /// <summary>
        /// 表数据委托方法定义
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public delegate List<object> DataHandler(SqlDataReader reader);
        public delegate string SingleDataHandler(SqlDataReader reader);

        /// 查询第三方表数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="whereClause"></param>
        /// <returns></returns>
        public static IEnumerable<object> ExcuteSqlCommand(string sqlText, DataHandler handler)
        {
            var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WhDbContext"].ConnectionString;
            var sqlConnection = new SqlConnection(connectionString);
            var resultList = new List<object>();

            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            // 基本的查询
            cmd.CommandText = sqlText;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = sqlConnection;

            sqlConnection.Open();

            reader = cmd.ExecuteReader();
            // 委托读取数据
            resultList = handler(reader);

            sqlConnection.Close();
            return resultList;
        }

        /// <summary>
        /// 执行SQL语句，没有代理
        /// </summary>
        /// <param name="sqlText"></param>
        public static bool ExcuteSqlCommand(string sqlText)
        {
            try
            {
                var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WhDbContext"].ConnectionString;
                var sqlConnection = new SqlConnection(connectionString);

                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader;

                // 基本的查询
                cmd.CommandText = sqlText;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = sqlConnection;

                sqlConnection.Open();

                reader = cmd.ExecuteReader();


                sqlConnection.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public static void ExecuteProcedure(string procName, Dictionary<string, object> paramDict)
        {
            try
            {
                var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WhDbContext"].ConnectionString;
                var sqlConnection = new SqlConnection(connectionString);

                var command = new SqlCommand();
                command.CommandText = procName;
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnection;

                // 参数赋值
                foreach(KeyValuePair<string, object> item in paramDict)
                {
                    command.Parameters.AddWithValue(item.Key, item.Value);
                }

                sqlConnection.Open();

                command.ExecuteNonQuery();

                sqlConnection.Close();
            }
            catch(Exception e)
            {
                throw new Exception(string.Format("存储过程出错：{0}, 详细信息:{1}", procName, e.Message));
            }
        }

        public static List<object>WKF_GlobalSortNo(SqlDataReader reader)
        {
            var ResultList = new List<object>();
            var dict = new Dictionary<string, string>();

            while(reader.Read())
            {
                string EmplID = reader.GetString(0);
                string EmplName = reader.GetString(1);
                dict.Add(EmplID, EmplName);    
            }

            ResultList.Add(dict);

            return ResultList;

        }

        /// <summary>
        /// 根据GlobalSortNo排序用户ID
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static List<object>WKF_OrderedSendList(SqlDataReader reader)
        {
            var ResultList = new List<object>();

            while (reader.Read())
            {
                string EmplID = reader.GetString(0);
                ResultList.Add(EmplID);
            }

            return ResultList;
        }

        public static List<object> WKF_EmployeeList(SqlDataReader reader)
        {
            var ResultList = new List<object>();
            while(reader.Read())
            {
                string EmplID = reader.GetString(0);
                ResultList.Add(EmplID);
            }
            return ResultList;
        }

        
        public static List<object> WKF_NotifyReceivers(SqlDataReader reader)
        {
            var ResultList = new List<object>();
            HashSet<object> set = new HashSet<object>();

            while(reader.Read())
            {
                string receivers = reader.GetString(0);
                List<string> splitArray = receivers.Split(',').ToList();
                foreach(var receiverID in splitArray)
                {
                    set.Add(receiverID);
                }
            }
            ResultList = set.ToList();
            return ResultList;
        }

        public static List<object> WKF_NotifyTargets(SqlDataReader reader)
        {
            var ResultList = new List<object>();
            while(reader.Read())
            {
                string emplID = reader.GetString(0);
                ResultList.Add(emplID);
            }
            return ResultList;
        }

        public static List<object> WKF_ChangedList(SqlDataReader reader)
        {
            var ResultList = new List<object>();
            var AddedNotificationList = new List<string>();

            while (reader.Read())
            {
                var MessageId = reader.GetString(0);
                var ClosedOrHairTime = reader.GetDateTime(1);
                var DocumentNumber = reader.GetString(2);
                var DocumentTitle = reader.GetString(3);
                var InitiateEmplId = reader.GetString(4);
                var InitiateEmplName = reader.GetString(5);
                var MessageTitle = reader.GetString(6);
                var MyTask = reader.GetString(7);
                var ReceiveTime = reader.GetDateTime(8);
                var SequenceName = reader.GetString(9);
                var SequenceNumber = reader.GetString(10);
                var WorkFlowId = reader.GetString(11);
                if(!AddedNotificationList.Contains(MessageId))
                {
                    ResultList.Add(new
                    {
                        MessageId = MessageId,
                        ClosedOrHairTime = ClosedOrHairTime,
                        DocumentNumber = DocumentNumber,
                        DocumentTitle = DocumentTitle,
                        InitiateEmplId = InitiateEmplId,
                        InitiateEmplName = InitiateEmplName,
                        MessageTitle = MessageTitle,
                        MyTask = MyTask,
                        ReceiveTime = ReceiveTime,
                        SequenceName = SequenceName,
                        SequenceNumber = SequenceNumber,
                        WorkFlowId = WorkFlowId
                    });
                    AddedNotificationList.Add(MessageId);
                }
            }
            return ResultList;
        }

        public static List<object> People_EmplListHandler(SqlDataReader reader)
        {
            var ResultList = new List<object>();
            while (reader.Read())
            {
                string emplID = reader.GetString(0);
                ResultList.Add(emplID);
            }
            return ResultList;
        }

        // 已收藏列表
        public static List<object> WKF_FavoriteList(SqlDataReader reader)
        {
            var ResultList = new List<object>();

            while (reader.Read())
            {
                var MessageId = reader.GetString(0);
                var ClosedOrHairTime = reader.GetDateTime(1);
                var DocumentNumber = reader.GetString(2);
                var DocumentTitle = reader.GetString(3);
                var InitiateEmplId = reader.GetString(4);
                var InitiateEmplName = reader.GetString(5);
                var MessageTitle = reader.GetString(6);
                var MyTask = reader.GetString(7);
                var ReceiveTime = reader.GetString(8);
                var SequenceName = reader.GetString(9);
                var SequenceNumber = reader.GetString(10);
                var WorkFlowId = reader.GetString(11);
                ResultList.Add(new
                {
                    MessageId = MessageId,
                    ClosedOrHairTime = ClosedOrHairTime,
                    DocumentNumber = DocumentNumber,
                    DocumentTitle = DocumentTitle,
                    InitiateEmplId = InitiateEmplId,
                    InitiateEmplName = InitiateEmplName,
                    MessageTitle = MessageTitle,
                    MyTask = MyTask,
                    ReceiveTime = ReceiveTime,
                    SequenceName = SequenceName,
                    SequenceNumber = SequenceNumber,
                    WorkFlowId = WorkFlowId
                });
            }
            return ResultList;
        }

        // 获得角色中成员
        public static List<object> ORG_RoleEmplList(SqlDataReader reader)
        {
            var ResultList = new List<object>();

            while(reader.Read())
            {
                var EmplID = reader.GetString(0);
                ResultList.Add(EmplID);
            }

            return ResultList;
        }
    }
}